﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    public class DangKyHocPhan
    {
        #region thuộc tính
        string _MSSV;
        public string MSSV
        {
            get { return _MSSV; }
            set { _MSSV = value; }
        }
        string _MaMH;
        public string MaMH
        {
            get { return _MaMH; }
            set { _MaMH = value; }
        }
        int _MaHK;
        public int MaHK
        {
            get { return _MaHK; }
            set { _MaHK = value; }
        }
        string _NamHoc;
        public string NamHoc
        {
            get { return _NamHoc; }
            set { _NamHoc = value; }
        }

        private static SqlConnection conn;
        private static SqlCommand cmd;
        #endregion
        public DangKyHocPhan()
        { }
        public DangKyHocPhan(string ms, string sMaMH, int sMaHK, string sNam)
        {
            MSSV = ms;
            MaMH = sMaMH;
            MaHK = sMaHK;
            NamHoc = sNam;
        }

        #region chức năng cơ bản
        private static List<DangKyHocPhan> list;
        public static int ThemDKHP(DangKyHocPhan dk)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_Them";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = dk.MSSV;
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = dk.MaMH;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = dk.MaHK;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = dk.NamHoc;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int CapNhatDKHP(DangKyHocPhan dk)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_CapNhat";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = dk.MSSV;
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = dk.MaMH;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = dk.MaHK;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = dk.NamHoc;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int XoaDKHP(string MSSV, string MaMH, int hk, string namhoc)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_Xoa";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = MSSV;
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = MaMH;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namhoc;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        private static DangKyHocPhan GetData(SqlDataReader read)
        {
            DangKyHocPhan data = new DangKyHocPhan();
            data.MSSV = CType.Tostring(read[0]);
            data.MaMH = CType.Tostring(read[1]);
            data.MaHK = CType.ToInt32(read[2]);
            data.NamHoc = CType.Tostring(read[3]);
            return data;
        }
        #endregion
        public static List<DangKyHocPhan> XemDKHPTheoSV(string MSSV)
        {
            list = new List<DangKyHocPhan>();
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_XemTheoSinhVien";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = MSSV;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    DangKyHocPhan data = GetData(read);
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<DangKyHocPhan> XemDKHPTheoMonHoc(string MaMH)
        {
            list = new List<DangKyHocPhan>();
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_XemTheoMonHoc";
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = MaMH;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    DangKyHocPhan data = GetData(read);
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<DangKyHocPhan> CacMonHocDaDangKyKyNay(string mssv, int hocky, string namhoc)
        {
            list = new List<DangKyHocPhan>();
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_DSTinChiDaDangKyKyNay";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hocky;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namhoc;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    DangKyHocPhan data = GetData(read);
                    list.Add(data);
                }
                return list;
            }
        }
        public static int TongSoTinChiDaDangKy(string mssv, int hk, string namhoc)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_TinhTongSoTC";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namhoc;
                conn.Open();
                int x = CType.ToInt32(cmd.ExecuteScalar());

                return x;
            }
        }
        public static int CoPhaiLaTinChiDangKyMoi(string MSSV, string MaMH, int hk, string nam)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_TCDangKyMoi";
                cmd.Parameters.Add("@kq", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = MaMH;
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = MSSV;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = nam;
                conn.Open();
                int x = cmd.ExecuteNonQuery();
                return (int)cmd.Parameters["@kq"].Value;
            }
        }
        public static int LayTCMonDangChon(string maMH)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "exec QuanLyDangKyHocPhan_LayTCMonHoc @MaMH";
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = maMH;
                conn.Open();
                int read = (int)cmd.ExecuteScalar();

                return read;
            }
        }
        public static int SoTinChiToiDa()
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "exec ThamSo_SoTCMax";
                conn.Open();
                int x = (int)cmd.ExecuteScalar();
                return x;
            }
        }
        public static int SoTinChiToiDaHocCaiThien()
        {
            using (conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "exec ThamSo_SoTCMax_CaiThien";
                conn.Open();
                int x = (int)cmd.ExecuteScalar();
                return x;
            }
        }
        public static int SoTinChiToiThieu()
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "exec ThamSo_SoTCMin";
                conn.Open();
                int x = (int)cmd.ExecuteScalar();
                return x;
            }
        }
        public static int DatThoiGianChoPhepDangKy(int hk, DateTime tu, DateTime den)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ThamSo_ThoiGianDangKy";
                cmd.Parameters.Add("@MaTS", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@BatDau", SqlDbType.DateTime).Value = tu;
                cmd.Parameters.Add("@KetThuc", SqlDbType.DateTime).Value = den;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int QuanLyDangKyHocPhan_DatSoTC(int ma, int sotc)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_DatSoTC";
                cmd.Parameters.Add("@MaTS", SqlDbType.Int).Value = ma;
                cmd.Parameters.Add("@GiaTri", SqlDbType.Int).Value = sotc;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static DateTime LayThoiGianKetThucDangKy(int hk)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = string.Format("select KetThuc from ThamSo_ThoiGian where MaTS={0}", hk);
                conn.Open();
                DateTime x = (DateTime)cmd.ExecuteScalar();
                return x;
            }
        }
        public static DateTime LayThoiGianBatDauDangKy(int hk)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = string.Format("select BatDau from ThamSo_ThoiGian where MaTS={0}", hk);
                conn.Open();
                DateTime x = (DateTime)cmd.ExecuteScalar();
                return x;
            }
        }
        //================================================================ tính toán điểm

        public static int TongSoTCHocKy(string mssv, int hk, string nam)
        {
            try
            {
                using (conn = MyConnection.MyConn())
                {
                    cmd = MyCommand.Command("QuanLyDangKyHocPhan_TinhTongSoTC", conn);
                    cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                    cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                    cmd.Parameters.Add("@NamHoc", SqlDbType.NChar, 10).Value = nam;
                    conn.Open();
                    int rs = (int)cmd.ExecuteScalar();
                    conn.Close();
                    return rs;
                }
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public static int TongTCTichLuy(string mssv)
        {
            try
            {
                using (conn = MyConnection.MyConn())
                {
                    cmd = MyCommand.Command("QuanLyDangKyHocPhan_TongTCTichLuy", conn);
                    cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                    conn.Open();
                    int rs = (int)cmd.ExecuteScalar();
                    conn.Close();
                    return rs;
                }
            }
            catch (Exception)
            {
                return 0;
            }

        }

        public static List<DangKyHocPhan> XemDKHPTheoMSSV(string mssv)
        {
            List<DangKyHocPhan> list = new List<DangKyHocPhan>();
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QuanLyDangKyHocPhan_XemTheoSinhVien", conn);
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DangKyHocPhan data = GetData(reader);
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<string> LayDanhSachNamDaHoc(string mssv)
        {
            List<string> list = new List<string>();
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QuanLyDangKyHocPhan_LayDanhSachNamDaHoc", conn);
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string data = (string)reader[0];
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<string> LayDSMaCacHocPhanDaHoc(string mssv)
        {
            List<string> list = new List<string>();
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QuanLyDangKyHocPhan_LayDSMaCacHocPhanDaHoc", conn);
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string data = (string)reader[0];
                    list.Add(data);
                }
                return list;
            }
            return list;
        }
        public static List<DangKyHocPhan> LayDSCacDKHPCungMaMH(string mssv, string mamh)
        {
            List<DangKyHocPhan> list = new List<DangKyHocPhan>();
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QuanLyDangKyHocPhan_LayDSCacDKHPCungMaMH", conn);
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mamh;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DangKyHocPhan data = GetData(reader);
                    list.Add(data);
                }
                return list;
            }
        }
        public static DataTable LayDSSinhVienTheoMonHoc(string mamh, int hk, string nam)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QLD_DiemThi", conn);
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mamh;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = nam;
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }
        public static DataTable QLD_LayDanhSachCotDiem(string mamh, int hk, string namhoc)
        {
            using (conn = MyConnection.MyConn())
            {
                cmd = MyCommand.Command("QLD_LayDanhSachCotDiem", conn);
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mamh;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 10).Value = namhoc;
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
        public static int QLD_LayDiemCotThuI(int maD, string mssv)
        {
            try
            {
                using (conn = MyConnection.MyConn())
                {
                    cmd = MyCommand.Command("QLD_LayDiemCotThuI", conn);
                    cmd.Parameters.Add("@MaD", SqlDbType.Int).Value = maD;
                    cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                    conn.Open();
                    int rs = int.Parse(cmd.ExecuteScalar().ToString());
                    conn.Close();
                    return rs;
                }
            }
            catch (Exception)
            {
                return -1;
            }

        }
        public static int ThemCotDiem(string mamh, int loai, int tile, int hk, string namhoc, int count)
        {
            try
            {
                using (SqlConnection conn = MyConnection.MyConn())
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "QuanLyDangKyHocPhan_ThemCotDiem";
                    cmd.Parameters.Add("@MaD", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mamh;
                    cmd.Parameters.Add("@Loai", SqlDbType.Int).Value = loai;
                    cmd.Parameters.Add("@Tile", SqlDbType.Int).Value = tile;
                    cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                    cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namhoc;
                    cmd.Parameters.Add("@Count", SqlDbType.Int).Value = count;

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    int after = (int)cmd.Parameters["MaD"].Value;
                    return after;
                }
            }
            catch
            {
                return -1;
            }

        }
        public static int ThemQuanLyDiem(string mssv, int MaD)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_ThemQuanLyThi";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                cmd.Parameters.Add("@MaD", SqlDbType.Int).Value = MaD;

                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }

    }
}
